/**
 * 
 */
package instant.mail.contact;

import instant.mail.EmailAddress;
import instant.mail.EmailContact;
import instant.mail.SQLDataException;
import instant.mail.Utils;

import java.util.Arrays;
import java.util.HashSet;
import java.util.Set;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.text.TextUtils;

/**
 * @author qiangli
 * 
 */
public class GroupContacts {
	public interface BaseColumns {
		public static final String _COUNT = "_count";
		public static final String _ID = "_id";
	}

	private static class DatabaseHelper extends SQLiteOpenHelper {
		DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			//Log.d(TAG, "DatabaseHelper onCreate");
			db.execSQL(DATABASE_CREATE);
			db.execSQL("CREATE INDEX groupNameIndex ON " + GROUP_TABLE + " ("
					+ Group.NAME + ");");
			//Log.d(TAG, "DatabaseHelper created.");
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			//Log.d(TAG, "DatabaseHelper onUpgrade. ignored. old: " + oldVersion
			//		+ " new: " + newVersion);
		}
	}
	public static final class Group implements BaseColumns, GroupColumns {
		public static long decodeId(long gid) {
			long id = gid - Long.MIN_VALUE;
			return id;
		}

		public static long encodeId(long id) {
			long gid = id + Long.MIN_VALUE;
			return gid;
		}

		private Group() {
		}
	}

	public interface GroupColumns {
		public static final String DATA1 = "data1";
		public static final String DATA2 = "data2";
		public static final String DATA3 = "data3";
		public static final String DATA4 = "data4";
		public static final String DATA5 = "data5";
		public static final String EMAILS = "emails";
		public static final String LAST_TIME_CONTACTED = "last_time_contacted";
		public static final String NAME = "name";
		public static final String PHOTO = "photo";
		public static final String RINGTONE = "ringtone";
		public static final String STARRED = "starred";
		public static final String TIMES_CONTACTED = "times_contacted";
	}

	private static final String GROUP_TABLE = "groups";

	private static final String DATABASE_CREATE = "CREATE TABLE " + GROUP_TABLE
			+ " (" + Group._ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
			+ Group.NAME + " TEXT COLLATE LOCALIZED," + Group.EMAILS + " TEXT,"
			+ Group.TIMES_CONTACTED + " INTEGER NOT NULL DEFAULT 0,"
			+ Group.LAST_TIME_CONTACTED + " INTEGER," + Group.STARRED
			+ " INTEGER NOT NULL DEFAULT 0," + Group.PHOTO + " TEXT,"
			+ Group.RINGTONE + " TEXT," + Group.DATA1 + " TEXT," + Group.DATA2
			+ " TEXT," + Group.DATA3 + " TEXT," + Group.DATA4 + " TEXT,"
			+ Group.DATA5 + " TEXT, UNIQUE (" + Group.NAME + ") );";

	private static final String DATABASE_NAME = "group.db";

	private static final int DATABASE_VERSION = 1;

	private static final String[] GROUP_COLUMNS = new String[] { Group._ID,
			Group.NAME, Group.EMAILS, Group.STARRED, Group.TIMES_CONTACTED,
			Group.LAST_TIME_CONTACTED, Group.PHOTO, Group.RINGTONE };


	static final String TAG = "GroupContacts";

	public static void addEmailToGroup(Context ctx, long gid, String[] emails) {
		GroupContacts gc = new GroupContacts(ctx);
		Cursor c = null;
		try {
			gc.open();
			long rowid = Group.decodeId(gid);
			c = gc.get(rowid);
			int colEmails = c.getColumnIndex(Group.EMAILS);
			HashSet<String> set = new HashSet<String>(Arrays.asList(emails));
			if (c.moveToFirst()) {
				String s = c.getString(colEmails);
				if (s != null) {
					String[] sa = Utils.toStringArray(s);
					set.addAll(Arrays.asList(sa));
				}
			}
			String[] na = set.toArray(new String[0]);
			Arrays.sort(na);
			String s = Utils.toString(na);
			gc.updateEmails(rowid, s);
		} finally {
			if (c != null) {
				c.close();
			}
			gc.close();
		}
	}
	public static long addGroup(Context ctx, String name, int starred,
			String[] emails) {
		if (name == null) {
			return -1;
		}
		if (!name.startsWith("@")) {
			name = "@" + name;
		}
		Arrays.sort(emails);
		//
		GroupContacts gc = new GroupContacts(ctx);
		long gid = -1;
		try {
			gc.open();
			String s = Utils.toString(emails);
			long rowid = gc.insert(name, s, starred);
			gid = Group.encodeId(rowid);
		} finally {
			gc.close();
		}
		return gid;
	}

	public static String[] getEmails(Context ctx, String name) {
		GroupContacts gc = new GroupContacts(ctx);
		Cursor c = null;
		try {
			gc.open();
			c = gc.get(name);
			int colEmails = c.getColumnIndex(Group.EMAILS);
			if (c.moveToFirst()) {
				String s = c.getString(colEmails);
				if (s != null) {
					return Utils.toStringArray(s);
				}
			}
		} finally {
			if (c != null) {
				c.close();
			}
			gc.close();
		}
		return null;
	}

	public static EmailContact getGroup(Context ctx, long gid) {
		GroupContacts gc = new GroupContacts(ctx);
		Cursor c = null;
		try {
			gc.open();
			long rowid = Group.decodeId(gid);
			c = gc.get(rowid);
			int colName = c.getColumnIndex(Group.NAME);
			int colStar = c.getColumnIndex(Group.STARRED);
			int colLastTime = c.getColumnIndex(Group.LAST_TIME_CONTACTED);
			int colTimes = c.getColumnIndex(Group.TIMES_CONTACTED);
			int colEmails = c.getColumnIndex(Group.EMAILS);
			if (c.moveToFirst()) {
				EmailContact ec = new EmailContact();
				ec.id = gid;
				ec.cid = gid;
				ec.name = c.getString(colName);
				ec.starred = c.getInt(colStar);
				ec.lastTime = c.getLong(colLastTime);
				ec.times = c.getInt(colTimes);
				String s = c.getString(colEmails);
				ec.emails = Utils.toStringArray(s);
				return ec;
			}
		} finally {
			if (c != null) {
				c.close();
			}
			gc.close();
		}
		return null;
	}

	public static long getIdByName(Context ctx, String name) {
		GroupContacts gc = new GroupContacts(ctx);
		Cursor c = null;
		try {
			gc.open();
			c = gc.get(name);
			int colId = c.getColumnIndex(Group._ID);

			if (c.moveToFirst()) {
				long rowid = c.getLong(colId);
				return Group.encodeId(rowid);
			}
		} finally {
			if (c != null) {
				c.close();
			}
			gc.close();
		}
		return -1;
	}

	public static String getPhoto(Context ctx, long gid) {
		GroupContacts gc = new GroupContacts(ctx);
		Cursor c = null;
		try {
			gc.open();
			long rowid = Group.decodeId(gid);
			c = gc.get(rowid);
			int col = c.getColumnIndex(Group.PHOTO);
			if (c.moveToFirst()) {
				return c.getString(col);
			}
		} finally {
			if (c != null) {
				c.close();
			}
			gc.close();
		}
		return null;
	}

	public static void loadAddresses(Context ctx, Set<EmailAddress> addresses) {
		GroupContacts gc = new GroupContacts(ctx);
		Cursor c = null;
		try {
			gc.open();
			c = gc.getAll();
			int colName = c.getColumnIndex(Group.NAME);
			int colEmails = c.getColumnIndex(Group.EMAILS);
			if (c.moveToFirst()) {
				do {
					String n = c.getString(colName);
					String s = c.getString(colEmails);
					if (s == null) {
						continue;
					}
					String[] sa = Utils.toStringArray(s);
					if (sa == null || sa.length == 0) {
						continue;
					}
					for (String a : sa) {
						addresses.add(new EmailAddress(a, n));
					}
				} while (c.moveToNext());
			}
		} finally {
			if (c != null) {
				c.close();
			}
			gc.close();
		}
	}

	public static void loadGroups(Context ctx, Set<EmailContact> contacts) {
		GroupContacts gc = new GroupContacts(ctx);
		Cursor c = null;
		try {
			gc.open();
			c = gc.getAll();
			int colId = c.getColumnIndex(Group._ID);
			int colName = c.getColumnIndex(Group.NAME);
			int colStar = c.getColumnIndex(Group.STARRED);
			int colLastTime = c.getColumnIndex(Group.LAST_TIME_CONTACTED);
			int colTimes = c.getColumnIndex(Group.TIMES_CONTACTED);
			int colEmails = c.getColumnIndex(Group.EMAILS);
			if (c.moveToFirst()) {
				do {
					EmailContact ec = new EmailContact();
					ec.id = Group.encodeId(c.getLong(colId));
					ec.cid = ec.id;
					ec.name = c.getString(colName);
					ec.starred = c.getInt(colStar);
					ec.lastTime = c.getLong(colLastTime);
					ec.times = c.getInt(colTimes);
					String s = c.getString(colEmails);
					ec.emails = Utils.toStringArray(s);
					//
					contacts.add(ec);
				} while (c.moveToNext());
			}
		} finally {
			if (c != null) {
				c.close();
			}
			gc.close();
		}
	}

	public static void loadGroupNames(Context ctx, Set<String> list) {
		GroupContacts gc = new GroupContacts(ctx);
		Cursor c = null;
		try {
			gc.open();
			c = gc.getAllNames();
			int colName = c.getColumnIndex(Group.NAME);
			int colEmails = c.getColumnIndex(Group.EMAILS);
			if (c.moveToFirst()) {
				do {
					String s = c.getString(colEmails);
					if (TextUtils.isEmpty(s)) {
						continue;
					}
					list.add(c.getString(colName));
				} while (c.moveToNext());
			}
		} finally {
			if (c != null) {
				c.close();
			}
			gc.close();
		}
	}

	public static int removeGroup(Context ctx, long gid) {
		GroupContacts gc = new GroupContacts(ctx);
		int rc = -1;
		try {
			gc.open();
			long rowid = Group.decodeId(gid);
			rc = gc.delete(rowid);
		} finally {
			gc.close();
		}
		return rc;
	}

	public static void starGroup(Context ctx, long gid, int starred) {
		GroupContacts gc = new GroupContacts(ctx);
		try {
			gc.open();
			long rowid = Group.decodeId(gid);
			gc.updateStar(rowid, starred);
		} finally {
			gc.close();
		}
	}

	public static void updatePhoto(Context ctx, long gid, String uri) {
		GroupContacts gc = new GroupContacts(ctx);
		try {
			gc.open();
			long rowid = Group.decodeId(gid);
			gc.updatePhoto(rowid, uri);
		} finally {
			gc.close();
		}
	}

	private final Context context;

	private SQLiteDatabase db;

	private DatabaseHelper helper;

	public GroupContacts(Context ctx) {
		this.context = ctx;
		helper = new DatabaseHelper(context);
	}

	public void close() {
		helper.close();
	}

	private int delete(long rowid) {
		return db.delete(GROUP_TABLE, Group._ID + "=" + rowid, null);
	}

	private Cursor get(long rowid) throws SQLException {
		Cursor c = db.query(true, GROUP_TABLE, GROUP_COLUMNS, Group._ID + "="
				+ rowid, null, null, null, null, null);
		return c;
	}

	private Cursor get(String name) throws SQLException {
		String sel = Group.NAME + " = ? ";
		String[] args = new String[] { name };
		Cursor c = db.query(true, GROUP_TABLE, GROUP_COLUMNS, sel, args, null,
				null, null, null);
		return c;
	}

	private Cursor getAll() {
		return db.query(GROUP_TABLE, GROUP_COLUMNS, null, null, null, null,
				null);
	}

	private Cursor getAllNames() {
		return db.query(true, GROUP_TABLE, new String[] { Group.NAME , Group.EMAILS}, null,
				null, null, null, null, null);
	}

	private long insert(String name, String emails, int starred) {
		ContentValues v = new ContentValues();
		v.put(Group.NAME, name);
		v.put(Group.EMAILS, emails);
		v.put(Group.STARRED, starred);
		long rowid = db.insert(GROUP_TABLE, null, v);
		if (rowid == -1) {
			throw new SQLDataException("insert: " + name);
		}
		return rowid;
	}

	public GroupContacts open() throws SQLException {
		db = helper.getWritableDatabase();
		return this;
	}

	private int updateEmails(long rowid, String emails) {
		ContentValues v = new ContentValues();
		v.put(Group.EMAILS, emails);
		return db.update(GROUP_TABLE, v, Group._ID + "=" + rowid, null);
	}

	private int updatePhoto(long rowid, String uri) {
		ContentValues v = new ContentValues();
		v.put(Group.PHOTO, uri);
		return db.update(GROUP_TABLE, v, Group._ID + "=" + rowid, null);
	}

	private int updateStar(long rowid, int starred) {
		ContentValues v = new ContentValues();
		v.put(Group.STARRED, starred);
		return db.update(GROUP_TABLE, v, Group._ID + "=" + rowid, null);
	}

	public static boolean hasEmailInGroup(Context ctx, String email) {
		GroupContacts gc = new GroupContacts(ctx);
		Cursor c = null;
		try {
			gc.open();
			c = gc.getAll();
			int colEmails = c.getColumnIndex(Group.EMAILS);
			if (c.moveToFirst()) {
				do {
					String s = c.getString(colEmails);
					if (s == null) {
						continue;
					}
					if (s.indexOf(email) == -1) {
						continue;
					}
					String[] sa = Utils.toStringArray(s);
					if (sa == null || sa.length == 0) {
						continue;
					}
					for (String a : sa) {
						if (email.equals(a)) {
							return true;
						}
					}
				} while (c.moveToNext());
			}
		} finally {
			if (c != null) {
				c.close();
			}
			gc.close();
		}
		return false;
	}

}
